﻿Public Class frmFormules

    Private szSQL As String
    Private rs As ADODB.Recordset
    Private m_SortingColumn As ColumnHeader
    Private m_SortingIndex As Integer = 0
    Private OLD_FORMULE As String

    Private Sub frmFormules_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        InitListeFormules(0)
        InitComboFilieres(CB_FILIERE)

    End Sub

    Private Sub InitListeFormules(ByVal ix As Integer)

        Dim nb As Integer = 0
        Dim szStage As String

        ' effacer la listview
        LV_FORMULES.Items.Clear()
        LV_FORMULES.ListViewItemSorter = Nothing

        ' Lire toutes les FORMULES selon leurs validité ou non
        If chk_EnCours.Checked = True Then szStage = "" Else szStage = "N"
        szSQL = "CALL spGet_FORMULES_valide('','" & szStage & "')"
        GetRecordset(ctDSN_AEM, szSQL, rs)

        ' Load the data.
        Do While Not rs.EOF
            If rs.Fields("stage").Value = 0 Then
                szStage = " "
            Else
                szStage = "Oui"
            End If
            LV_FORMULES.Items.Add(rs.Fields("formule").Value)
            LV_FORMULES.Items(nb).SubItems.Add(rs.Fields("libelle").Value)
            LV_FORMULES.Items(nb).SubItems.Add(szStage)
            LV_FORMULES.Items(nb).SubItems.Add(rs.Fields("DateFin").Value)
            ' formule valide ?
            If DateDiff(DateInterval.Day, rs.Fields("DateDeb").Value, Now) <= 0 Or _
                DateDiff(DateInterval.Day, rs.Fields("DateFin").Value, Now) > 0 Then
                LV_FORMULES.Items(nb).ForeColor = Color.Gray
            Else
                LV_FORMULES.Items(nb).ForeColor = Color.Navy
            End If
            LV_FORMULES.Items(nb).SubItems.Add(rs.Fields("filiere").Value)
            rs.MoveNext()
            nb = nb + 1
        Loop
        ' Close the recordset AND connection.
        rs.Close()

        LB_NbLignes.Text = CStr(nb) & " lignes"
        ' restaurer l'ancienne ordre de tri de la listview
        If m_SortingIndex > 0 Then
            SortListView(LV_FORMULES, m_SortingColumn, m_SortingIndex)
        End If
        ' restaurer la position du curseur avant le init
        setIndice(ix, LV_FORMULES)

    End Sub

    Private Sub LV_FORMULES_ColumnClick(ByVal sender As Object, ByVal e As System.Windows.Forms.ColumnClickEventArgs) Handles LV_FORMULES.ColumnClick
        SortListView(LV_FORMULES, m_SortingColumn, e.Column)
        m_SortingIndex = e.Column
    End Sub

    Private Sub LV_Formules_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles LV_FORMULES.DoubleClick

        If LV_FORMULES.SelectedItems.Count > 0 Then
            FormuletoScreen(LV_FORMULES.SelectedItems(0).Text)
            ' afficher les détails de la formule
            tb_details.SelectedIndex = 0
            tb_details.Visible = True
            TE_FORMULE.Focus()
            ' mémoriser ancien identifiant pour maj
            OLD_FORMULE = TE_FORMULE.Text
            ' utilisateur autorisé à modifier ?
            If gUserAcces < 9 Then
                BO_ENREGISTER.Enabled = False
            Else
                BO_ENREGISTER.Enabled = True
                BO_ANNULER.Enabled = True
            End If
            BO_ENREGISTER.Visible = True
            BO_ANNULER.Visible = True
        End If

    End Sub

    Private Sub LV_Formules_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles LV_FORMULES.KeyDown

        Select Case e.KeyCode
            ' touche Enter = DoubleClick
            Case Keys.Enter
                LV_Formules_DoubleClick(sender, e)

            Case Keys.Insert
                BO_ADD_LIGNE_Click(sender, e)

            Case Keys.Delete
                BO_DELETE_LIGNE_Click(sender, e)

        End Select

    End Sub

    Private Sub chk_EnCours_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chk_EnCours.CheckedChanged
        InitListeFormules(0)
    End Sub

    Private Sub FormuletoScreen(ByVal formule As String)

        ' Lire la FORMULE sélectionnée
        szSQL = "CALL spGet_FORMULES('" & formule & "')"
        GetRecordset(ctDSN_AEM, szSQL, rs)
        If rs.RecordCount > 0 Then
            TE_FORMULE.Text = rs.Fields("formule").Value
            TE_LIB.Text = rs.Fields("libelle").Value
            TE_CONTRAT.Text = rs.Fields("libelle").Value
            chk_Stage.Checked = rs.Fields("stage").Value
            chk_FORFAIT_CODE.Checked = rs.Fields("forfait_code").Value
            TE_datedeb.Value = rs.Fields("datedeb").Value
            TE_datefin.Value = rs.Fields("datefin").Value
            TE_INS1.Text = nv(rs.Fields("ins1").Value)
            TE_INS2.Text = nv(rs.Fields("ins2").Value)
            TE_INS3.Text = nv(rs.Fields("ins3").Value)
            TE_INS4.Text = nv(rs.Fields("ins4").Value)
            TE_MTVAL1.Text = rs.Fields("mtval1").Value
            TE_MTVAL2.Text = rs.Fields("mtval2").Value
            TE_MTVAL3.Text = rs.Fields("mtval3").Value
            TE_MTVAL4.Text = rs.Fields("mtval4").Value
            TE_NBPTGRATUIT.Text = rs.Fields("NBPTGRATUIT").Value
            TE_NBPPGRATUIT.Text = rs.Fields("NBPPGRATUIT").Value
            TE_NBHCGRATUIT.Text = rs.Fields("NBHCGRATUIT").Value
            TE_NBCCGRATUIT.Text = rs.Fields("NBCCGRATUIT").Value
            TE_NBHCGRATUIT.Text = rs.Fields("NBHCGRATUIT").Value
            TE_NBHRGRATUIT.Text = rs.Fields("NBHRGRATUIT").Value
            TE_VAL_HCG.Text = rs.Fields("val_hcg").Value
            TE_VAL_CCG.Text = rs.Fields("val_ccg").Value
            TE_VAL_HRG.Text = rs.Fields("val_hrg").Value
            TE_HSeance.Text = rs.Fields("HSeance").Value
            TE_TFISC.Text = nv(rs.Fields("tfisc").Value)
            TE_P_THEOS.Text = nv(rs.Fields("p_theos").Value)
            TE_P_PRATS1.Text = nv(rs.Fields("p_prats1").Value)
            TE_P_PRATS.Text = nv(rs.Fields("p_prats").Value)
            TE_HCONDUIT.Text = nv(rs.Fields("hconduit").Value)
            TE_DUREE_CODE.Text = rs.Fields("duree_code").Value
            TE_DUREE_COND.Text = rs.Fields("duree_cond").Value
            TE_CONTRAT1.Text = nv(rs.Fields("contrat1").Value)
            TE_CONTRAT2.Text = nv(rs.Fields("contrat2").Value)
            CB_TYPE_EXAMEN.SelectedIndex = CB_TYPE_EXAMEN.FindString(rs.Fields("type_examen").Value)
            CB_FILIERE.SelectedIndex = CB_FILIERE.FindString(rs.Fields("filiere").Value)
            TE_MODIFIE_LE.Text = setModifiedBy(rs.Fields("cree_le").Value, rs.Fields("modif_le").Value, rs.Fields("cd_uti").Value)
        End If
        rs.Close()

    End Sub

    Private Function IsInputOK() As Boolean

        IsInputOK = False

        ' dates de validation
        If DateDiff(DateInterval.Day, TE_datedeb.Value, TE_datefin.Value) < 0 Then
            MessageBox.Show("La date de fin ne peut être antérieure à la date de début", "Dates de validité", _
                        MessageBoxButtons.OKCancel, MessageBoxIcon.Question)
            Exit Function
        End If
        ' montants
        If IsNumeric(TE_MTVAL1.Text) = False Or IsNumeric(TE_MTVAL2.Text) = False Or IsNumeric(TE_MTVAL3.Text) = False Or _
            IsNumeric(TE_VAL_HCG.Text) = False Or IsNumeric(TE_VAL_CCG.Text) = False Or IsNumeric(TE_VAL_HRG.Text) = False Then
            MessageBox.Show("Un des montants n'est pas valide", "Montants", _
                        MessageBoxButtons.OKCancel, MessageBoxIcon.Error)
            Exit Function
        End If
        If IsNumeric(TE_DUREE_CODE.Text) = False Or IsNumeric(TE_DUREE_COND.Text) = False Then
            MessageBox.Show("La durée de la formule est n'est pas valide", "Contrôle", _
                       MessageBoxButtons.OKCancel, MessageBoxIcon.Error)
            Exit Function
        End If
        IsInputOK = True

    End Function

    Private Sub BO_PARCOURIR1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_PARCOURIR1.Click
        Dim openDataFileDialog As OpenFileDialog = New OpenFileDialog()
        openDataFileDialog.Filter = "Fichiers Word ou Crystal|*.doc;*.rpt"
        openDataFileDialog.InitialDirectory = Mid(My.Application.Info.DirectoryPath, 1, 10) & "\CONTRATS\"
        If openDataFileDialog.ShowDialog = _
        Windows.Forms.DialogResult.OK Then
            TE_CONTRAT1.Text = Mid(openDataFileDialog.FileName, 3)
        Else
            TE_CONTRAT1.Clear()
        End If
    End Sub

    Private Sub BO_PARCOURIR2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_PARCOURIR2.Click
        Dim openDataFileDialog As OpenFileDialog = New OpenFileDialog()
        openDataFileDialog.Filter = "Fichiers Word ou Crystal|*.doc;*.rpt"
        openDataFileDialog.InitialDirectory = Mid(My.Application.Info.DirectoryPath, 1, 10) & "\CONTRATS\"
        If openDataFileDialog.ShowDialog = _
        Windows.Forms.DialogResult.OK Then
            TE_CONTRAT2.Text = Mid(openDataFileDialog.FileName, 3)
        Else
            TE_CONTRAT2.Clear()
        End If
    End Sub

    Private Sub BO_ENREGISTRER_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_ENREGISTER.Click
        ' Controler la saisie
        If IsInputOK() = False Then
            Exit Sub
        End If

        ' mise à jour de la nouvelle formulle
        szSQL = "UPDATE FORMULES set formule = '" & TE_FORMULE.Text & "',libelle='" & TE_LIB.Text & "',forfait_code=" & CInt(chk_FORFAIT_CODE.Checked) & _
            ",stage=" & CInt(chk_Stage.Checked) & ",datedeb='" & Format(TE_datedeb.Value, "yyyy/MM/dd") & "',datefin='" & Format(TE_datefin.Value, "yyyy/MM/dd") & _
            "',ins1='" & TE_INS1.Text & "',ins2='" & TE_INS2.Text & "',ins3='" & TE_INS3.Text & "',ins4='" & TE_INS4.Text & "',type_examen='" & CB_TYPE_EXAMEN.SelectedItem & _
            "',mtval1=" & TE_MTVAL1.Text & ",mtval2=" & TE_MTVAL2.Text & ",mtval3=" & TE_MTVAL3.Text & ",mtval4=" & TE_MTVAL4.Text & _
            ",nbptgratuit=" & TE_NBPTGRATUIT.Text & ",nbppgratuit=" & TE_NBPPGRATUIT.Text & ",nbhcgratuit=" & TE_NBHCGRATUIT.Text & _
            ",nbccgratuit=" & TE_NBCCGRATUIT.Text & ",nbhrgratuit=" & TE_NBHRGRATUIT.Text & ",hseance=" & TE_HSeance.Text & _
            ",val_hcg=" & TE_VAL_HCG.Text & ",val_ccg=" & TE_VAL_CCG.Text & ",val_hrg=" & TE_VAL_HRG.Text & _
            ",tfisc='" & TE_TFISC.Text & "',p_theos='" & TE_P_THEOS.Text & "',p_prats1='" & TE_P_PRATS1.Text & _
            "',p_prats='" & TE_P_PRATS.Text & "',hconduit='" & TE_HCONDUIT.Text & "',contrat1='" & ns(TE_CONTRAT1.Text) & "',contrat2='" & TE_CONTRAT2.Text & _
            "',filiere='" & CB_FILIERE.SelectedItem & "',duree_code=" & TE_DUREE_CODE.Text & ",duree_cond=" & TE_DUREE_COND.Text & _
            " WHERE formule ='" & OLD_FORMULE & "'"
        ExecQuery(ctDSN_AEM, szSQL)
        ' Cacher les détails
        BO_ANNULER_Click1(sender, e)

    End Sub

    Private Sub BO_ANNULER_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Handles BO_ANNULER.Click
        ' Cacher les détails
        tb_details.Visible = False
        ' réaffiche la viewlist
        InitListeFormules(LV_FORMULES.SelectedItems(0).Index)
        BO_ENREGISTER.Visible = False
        BO_ANNULER.Visible = False
    End Sub

    Private Sub BO_ADD_LIGNE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_ADD_LIGNE.Click
        ' utilisateur autorisé à ajouter ?
        If IsUserAuthorised(9) = False Then
            Exit Sub
        End If
        ' Ajouter une nouvelle formulle
        szSQL = "spINS_FORMULES('" & Format(Now, "yyyy/MM/dd") & "', '" & Format(Now.AddMonths(6), "yyyy/MM/dd") & "')"
        If ExecQuery(ctDSN_AEM, szSQL) Then
            ' réaffiche la viewlist
            InitListeFormules(0)
            ' ouvrir le fomulaire de modif
            LV_Formules_DoubleClick(sender, e)
        End If
    End Sub

    Private Sub BO_DELETE_LIGNE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BO_DELETE_LIGNE.Click
        ' utilisateur autorisé à supprimer ?
        If IsUserAuthorised(9) = False Then
            Exit Sub
        End If
        deleteEnregistrement("FORMULES", LV_FORMULES.SelectedItems(0).SubItems(1).Text, LV_FORMULES.SelectedItems(0).Text)
        ' cacher les détails
        BO_ANNULER_Click1(sender, e)
    End Sub
End Class
